package com.sxhuayuan.parking.utils;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * excel导出工具<br/>
 * 
 * 使用教程：<br/>
 * 
 * @see #setTitle(XSSFSheet sheet, String title, int rowNum, int colNum)
 *      1.设置标题<br/>
 * @see #setHead(XSSFSheet, List, int, Integer) 2.设置表头<br/>
 * @see #setDatas(XSSFSheet, List, int, Integer) 3.填充数据<br/>
 * @see #sendExcelToClient(HttpServletResponse, XSSFWorkbook, String)
 *      4.将Excel发送至客户端<br/>
 * @author 何龙
 * @create 2019-05-31 11:22
 */
public class ExcelUtils {

	private static final int DEFAULT_COLUMN_SIZE = 30;

	private static final int DEFAULT_ROW_HEIGHT = 20;

	/**
	 * 断言Excel文件写入之前的条件
	 *
	 * @param directory
	 *            目录
	 * @param fileName
	 *            文件名
	 * @return file
	 * @throws IOException
	 */
	private static File assertFile(String directory, String fileName) throws IOException {
		File tmpFile = new File(directory + File.separator + fileName + ".xlsx");
		if (tmpFile.exists()) {
			if (tmpFile.isDirectory()) {
				throw new IOException("File '" + tmpFile + "' exists but is a directory");
			}
			if (!tmpFile.canWrite()) {
				throw new IOException("File '" + tmpFile + "' cannot be written to");
			}
		} else {
			File parent = tmpFile.getParentFile();
			if (parent != null) {
				if (!parent.mkdirs() && !parent.isDirectory()) {
					throw new IOException("Directory '" + parent + "' could not be created");
				}
			}
		}
		return tmpFile;
	}

	/**
	 * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
	 */
	private static String getCnDate(Date date) {
		String format = "yyyy-MM-dd HH:mm:ss";
		SimpleDateFormat sdf = new SimpleDateFormat(format);
		return sdf.format(date);
	}

	/**
	 * Excel 导出，POI实现
	 *
	 * @param fileName
	 *            文件名
	 * @param sheetName
	 *            sheet页名称
	 * @param columnNames
	 *            表头列表名
	 * @param sheetTitle
	 *            sheet页Title
	 * @param objects
	 *            目标数据集
	 */
	public static File writeExcel(String directory, String fileName, String sheetName, List<String> columnNames, String sheetTitle, List<List<Object>> objects, boolean append)
			throws IOException {
		File tmpFile = assertFile(directory, fileName);
		return exportExcel(tmpFile, sheetName, columnNames, sheetTitle, objects, append);
	}

	/**
	 * Excel 导出，POI实现，先写入Excel标题，与writeExcelData配合使用
	 * 先使用writeExcelTitle再使用writeExcelData
	 *
	 * @param directory
	 *            目录
	 * @param fileName
	 *            文件名
	 * @param sheetName
	 *            sheetName
	 * @param columnNames
	 *            列名集合
	 * @param sheetTitle
	 *            表格标题
	 * @param append
	 *            是否在现有的文件追加
	 * @return file
	 * @throws IOException
	 */
	public static File writeExcelTitle(String directory, String fileName, String sheetName, List<String> columnNames, String sheetTitle, boolean append) throws IOException {
		File tmpFile = assertFile(directory, fileName);
		return exportExcelTitle(tmpFile, sheetName, columnNames, sheetTitle, append);
	}

	/**
	 * Excel 导出，POI实现，写入Excel数据行列，与writeExcelTitle配合使用
	 * 先使用writeExcelTitle再使用writeExcelData
	 *
	 * @param directory
	 *            目录
	 * @param fileName
	 *            文件名
	 * @param sheetName
	 *            sheetName
	 * @param objects
	 *            数据信息
	 * @return file
	 * @throws IOException
	 */
	public static File writeExcelData(String directory, String fileName, String sheetName, List<List<Object>> objects) throws IOException {
		File tmpFile = assertFile(directory, fileName);
		return exportExcelData(tmpFile, sheetName, objects);
	}

	/**
	 * 导出字符串数据
	 *
	 * @param file
	 *            文件名
	 * @param columnNames
	 *            表头
	 * @param sheetTitle
	 *            sheet页Title
	 * @param append
	 *            是否追加写文件
	 * @return file
	 */
	private static File exportExcelTitle(File file, String sheetName, List<String> columnNames, String sheetTitle, boolean append) throws IOException {
		// 声明一个工作薄
		Workbook workBook;
		if (file.exists() && append) {
			workBook = new XSSFWorkbook(new FileInputStream(file));
		} else {
			workBook = new XSSFWorkbook();
		}
		Map<String, CellStyle> cellStyleMap = styleMap(workBook);
		// 表头样式
		CellStyle headStyle = cellStyleMap.get("head");
		// 生成一个表格
		Sheet sheet = workBook.getSheet(sheetName);
		if (sheet == null) {
			sheet = workBook.createSheet(sheetName);
		}
		// 最新Excel列索引,从0开始
		int lastRowIndex = sheet.getLastRowNum();
		if (lastRowIndex > 0) {
			lastRowIndex++;
		}
		// 设置表格默认列宽度
		sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
		// 产生表格标题行
		Row rowMerged = sheet.createRow(lastRowIndex);
		lastRowIndex++;
		Cell mergedCell = rowMerged.createCell(0);
		mergedCell.setCellStyle(headStyle);
		mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
		// 产生表格表头列标题行
		Row row = sheet.createRow(lastRowIndex);
		for (int i = 0; i < columnNames.size(); i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(headStyle);
			RichTextString text = new XSSFRichTextString(columnNames.get(i));
			cell.setCellValue(text);
		}
		OutputStream ops = new FileOutputStream(file);
		workBook.write(ops);
		ops.flush();
		ops.close();
		return file;
	}

	/**
	 * 导出字符串数据
	 *
	 * @param file
	 *            文件名
	 * @param objects
	 *            目标数据
	 * @return
	 */
	private static File exportExcelData(File file, String sheetName, List<List<Object>> objects) throws IOException {
		// 声明一个工作薄
		Workbook workBook;
		if (file.exists()) {
			workBook = new XSSFWorkbook(new FileInputStream(file));
		} else {
			workBook = new XSSFWorkbook();
		}

		Map<String, CellStyle> cellStyleMap = styleMap(workBook);
		// 正文样式
		CellStyle contentStyle = cellStyleMap.get("content");
		// 正文整数样式
		CellStyle contentIntegerStyle = cellStyleMap.get("integer");
		// 正文带小数整数样式
		CellStyle contentDoubleStyle = cellStyleMap.get("double");
		// 生成一个表格
		Sheet sheet = workBook.getSheet(sheetName);
		if (sheet == null) {
			sheet = workBook.createSheet(sheetName);
		}
		// 最新Excel列索引,从0开始
		int lastRowIndex = sheet.getLastRowNum();
		if (lastRowIndex > 0) {
			lastRowIndex++;
		}
		// 设置表格默认列宽度
		sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
		// 遍历集合数据,产生数据行,前两行为标题行与表头行
		for (List<Object> dataRow : objects) {
			Row row = sheet.createRow(lastRowIndex);
			lastRowIndex++;
			for (int j = 0; j < dataRow.size(); j++) {
				Cell contentCell = row.createCell(j);
				Object dataObject = dataRow.get(j);
				if (dataObject != null) {
					if (dataObject instanceof Integer) {
						contentCell.setCellStyle(contentIntegerStyle);
						contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
					} else if (dataObject instanceof Double) {
						contentCell.setCellStyle(contentDoubleStyle);
						contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
					} else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
					} else if (dataObject instanceof Date) {
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(getCnDate((Date) dataObject));
					} else {
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(dataObject.toString());
					}
				} else {
					contentCell.setCellStyle(contentStyle);
					// 设置单元格内容为字符型
					contentCell.setCellValue("");
				}
			}
		}
		OutputStream ops = new FileOutputStream(file);
		workBook.write(ops);
		ops.flush();
		ops.close();
		return file;
	}

	/**
	 * 导出字符串数据
	 *
	 * @param file
	 *            文件名
	 * @param columnNames
	 *            表头
	 * @param sheetTitle
	 *            sheet页Title
	 * @param objects
	 *            目标数据
	 * @param append
	 *            是否追加写文件
	 * @return
	 */
	private static File exportExcel(File file, String sheetName, List<String> columnNames, String sheetTitle, List<List<Object>> objects, boolean append) throws IOException {
		// 声明一个工作薄
		Workbook workBook;
		if (file.exists() && append) {
			// 声明一个工作薄
			workBook = new XSSFWorkbook(new FileInputStream(file));
		} else {
			workBook = new XSSFWorkbook();
		}
		Map<String, CellStyle> cellStyleMap = styleMap(workBook);
		// 表头样式
		CellStyle headStyle = cellStyleMap.get("head");
		// 正文样式
		CellStyle contentStyle = cellStyleMap.get("content");
		// 正文整数样式

		CellStyle contentIntegerStyle = cellStyleMap.get("integer");
		// 正文带小数整数样式
		CellStyle contentDoubleStyle = cellStyleMap.get("double");
		// 生成一个表格
		Sheet sheet = workBook.getSheet(sheetName);
		if (sheet == null) {
			sheet = workBook.createSheet(sheetName);
		}
		// 最新Excel列索引,从0开始
		int lastRowIndex = sheet.getLastRowNum();
		if (lastRowIndex > 0) {
			lastRowIndex++;
		}
		// 设置表格默认列宽度
		sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
		// 产生表格标题行
		Row rowMerged = sheet.createRow(lastRowIndex);
		lastRowIndex++;
		Cell mergedCell = rowMerged.createCell(0);
		mergedCell.setCellStyle(headStyle);
		mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
		// 产生表格表头列标题行
		Row row = sheet.createRow(lastRowIndex);
		lastRowIndex++;
		for (int i = 0; i < columnNames.size(); i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(headStyle);
			RichTextString text = new XSSFRichTextString(columnNames.get(i));
			cell.setCellValue(text);
		}
		// 遍历集合数据,产生数据行,前两行为标题行与表头行
		for (List<Object> dataRow : objects) {
			row = sheet.createRow(lastRowIndex);
			lastRowIndex++;
			for (int j = 0; j < dataRow.size(); j++) {
				Cell contentCell = row.createCell(j);
				Object dataObject = dataRow.get(j);
				if (dataObject != null) {
					if (dataObject instanceof Integer) {
						contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
						contentCell.setCellStyle(contentIntegerStyle);
						contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
					} else if (dataObject instanceof Double) {
						contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
						contentCell.setCellStyle(contentDoubleStyle);
						contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
					} else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
						contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
					} else if (dataObject instanceof Date) {
						contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(getCnDate((Date) dataObject));
					} else {
						contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
						contentCell.setCellStyle(contentStyle);
						contentCell.setCellValue(dataObject.toString());
					}
				} else {
					contentCell.setCellStyle(contentStyle);
					// 设置单元格内容为字符型
					contentCell.setCellValue("");
				}
			}
		}
		OutputStream ops = new FileOutputStream(file);
		workBook.write(ops);
		ops.flush();
		ops.close();
		return file;
	}

	/**
	 * 创建大标题样式
	 * 
	 * @param wb
	 * @return
	 */
	public static CellStyle createBigTitleStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 16);
		font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 字体加粗
		style.setFont(font);
		style.setAlignment(CellStyle.ALIGN_CENTER); // 横向居中
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 纵向居中
		return style;
	}

	/**
	 * 创建合并单元格样式
	 * 
	 * @param wb
	 * @return
	 */
	public static CellStyle createMergedLatticeStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		Font headFont = wb.createFont();
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 11);
		style.setFont(headFont);
		// 水平居中
		style.setAlignment(CellStyle.ALIGN_CENTER);
		// 垂直居中
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		return style;
	}

	/**
	 * 创建单元格表头样式
	 *
	 * @param workbook
	 *            工作薄
	 */
	private static CellStyle createCellHeadStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
		// 设置对齐样式
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		// 生成字体
		Font font = workbook.createFont();
		// 表头样式
		style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}

	/**
	 * 创建单元格正文样式
	 *
	 * @param workbook
	 *            工作薄
	 */
	private static CellStyle createCellContentStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		// 设置对齐样式
		style.setAlignment(CellStyle.ALIGN_CENTER);
		// 生成字体
		Font font = workbook.createFont();
		// 正文样式
		style.setFillPattern(CellStyle.NO_FILL);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}

	/**
	 * 单元格样式(Integer)列表
	 */
	private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
		// 设置对齐样式
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 生成字体
		Font font = workbook.createFont();
		// 正文样式
		style.setFillPattern(XSSFCellStyle.NO_FILL);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style.setFont(font);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));// 数据格式只显示整数
		return style;
	}

	/**
	 * 单元格样式(Double)列表
	 */
	private static CellStyle createCellContent4DoubleStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
		// 设置对齐样式
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 生成字体
		Font font = workbook.createFont();
		// 正文样式
		style.setFillPattern(XSSFCellStyle.NO_FILL);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style.setFont(font);
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));// 保留两位小数点
		return style;
	}

	/**
	 * 单元格样式(Long)列表
	 */
	private static CellStyle createCellContentLongStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 设置边框样式
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);
		// 设置对齐样式
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 生成字体
		Font font = workbook.createFont();
		// 正文样式
		style.setFillPattern(XSSFCellStyle.NO_FILL);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}

	/**
	 * 单元格样式列表
	 */
	private static Map<String, CellStyle> styleMap(Workbook workbook) {
		Map<String, CellStyle> styleMap = new LinkedHashMap<>();
		styleMap.put("head", createCellHeadStyle(workbook));
		styleMap.put("content", createCellContentStyle(workbook));
		styleMap.put("integer", createCellContent4IntegerStyle(workbook));
		styleMap.put("double", createCellContent4DoubleStyle(workbook));
		return styleMap;
	}

	/**
	 * 设置标题
	 * 
	 * @param sheet
	 * @param title
	 *            标题
	 * @param rowNum
	 *            标题跨多少行
	 * @param colNum
	 *            标题跨多少列
	 */
	public static void setTitle(XSSFSheet sheet, String title, int rowNum, int colNum) {
		if (sheet == null) {
			return;
		}
		rowNum = rowNum < 1 ? 0 : rowNum - 1;
		colNum = colNum < 1 ? 0 : colNum - 1;
		XSSFCell cell = sheet.createRow(0).createCell(0);
		// 填充标题
		cell.setCellValue(title);
		// 设置标题样式
		cell.setCellStyle(createBigTitleStyle(sheet.getWorkbook()));
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(0, rowNum, 0, colNum));
	}

	/**
	 * 设置表头
	 * 
	 * @param sheet
	 *            sheet
	 * @param heads
	 *            表头参数
	 * @param rowNum
	 *            从第几行开始设置为表头
	 * @param rowHeight
	 *            行高
	 */
	public static void setHead(XSSFSheet sheet, List<String> heads, int rowNum, Integer rowHeight) {
		if (sheet == null) {
			return;
		}
		assert heads != null;
		rowNum = rowNum < 0 ? 0 : rowNum;
		XSSFRow row = sheet.createRow(rowNum);
		// 设置行高
		row.setHeightInPoints(rowHeight == null ? 24 : rowHeight);
		for (int i = 0; i < heads.size(); i++) {
			XSSFCell cell = row.createCell(i);
			// 填充表头
			cell.setCellValue(heads.get(i));
			// 设置表头样式
			cell.setCellStyle(createCellHeadStyle(sheet.getWorkbook()));
		}
	}

	/**
	 * 填充多行数据<br/>
	 * 1000行数据以上，为防止内存溢出，丢弃单元格样式
	 *
	 * @param sheet
	 *            sheet
	 * @param objectList
	 *            多行数据
	 * @param rowNum
	 *            行起始索引
	 * @param rowHeight
	 *            行高
	 * @return sheet
	 */
	public static XSSFSheet setDatas(XSSFSheet sheet, List<List<Object>> objectList, int rowNum, Integer rowHeight) {
		assert objectList != null;
		rowNum = rowNum < 0 ? 0 : rowNum;
		if (objectList.size() <= 1000) {
			for (List<Object> list : objectList) {
				setRowDatas(sheet.createRow(rowNum++), list, rowHeight);
				list.clear();
			}
		} else {
			for (List<Object> list : objectList) {
				setRowDatas2(sheet.createRow(rowNum++), list, rowHeight);
				list.clear();
			}
		}
		return sheet;
	}

	/**
	 * 设置一行数据
	 * 
	 * @param row
	 *            行对象
	 * @param objectList
	 *            数据
	 * @param rowHeight
	 *            行高
	 */
	public static void setRowDatas(XSSFRow row, List<Object> objectList, Integer rowHeight) {
		int size = objectList.size();
		// 设置行高
		row.setHeightInPoints(rowHeight == null ? DEFAULT_ROW_HEIGHT : rowHeight);
		for (int i = 0; i < size; i++) {
			setCellValue(row, i, objectList.get(i));
		}
		objectList.clear();
	}

	/**
	 * 无样式的单元格
	 */
	public static void setRowDatas2(XSSFRow row, List<Object> objectList, Integer rowHeight) {
		int size = objectList.size();
		// 设置行高
		row.setHeightInPoints(rowHeight == null ? DEFAULT_ROW_HEIGHT : rowHeight);
		for (int i = 0; i < size; i++) {
			setCellValue2(row, i, objectList.get(i));
		}
	}

	/**
	 * 给单元格赋值、根据单元格类型，设置不同样式
	 * 
	 * @param row
	 *            行对象
	 * @param cellNum
	 *            列索引，从0开始
	 * @param dataObject
	 *            单元格数据
	 */
	private static void setCellValue(XSSFRow row, int cellNum, Object dataObject) {
		XSSFSheet sheet = row.getSheet();
		XSSFWorkbook workbook = sheet.getWorkbook();
		XSSFCell cell = row.createCell(cellNum);
		if (dataObject != null) {
			if (dataObject instanceof Integer) {
				cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(createCellContent4IntegerStyle(workbook));
				cell.setCellValue(Integer.parseInt(dataObject.toString()));
			} else if (dataObject instanceof Double) {
				cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(createCellContent4DoubleStyle(workbook));
				cell.setCellValue(Double.parseDouble(dataObject.toString()));
			} else if (dataObject instanceof Long) {
				cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(createCellContentLongStyle(workbook));
				cell.setCellValue((Long) dataObject);
			} else if (dataObject instanceof Date) {
				cell.setCellType(XSSFCell.CELL_TYPE_STRING);
				XSSFCellStyle style = workbook.createCellStyle();
				style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
				cell.setCellStyle(style);
				cell.setCellValue(getCnDate((Date) dataObject));
			} else if (dataObject instanceof LocalDate) {
				cell.setCellType(XSSFCell.CELL_TYPE_STRING);
				XSSFCellStyle style = workbook.createCellStyle();
				style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
				cell.setCellStyle(style);
				cell.setCellValue(((LocalDate) dataObject).format(DateTimeFormatter.ofPattern("m/d/yy h:mm")));
			} else {
				cell.setCellType(XSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(createCellContentStyle(workbook));
				cell.setCellValue(dataObject.toString());
			}
		} else {
			XSSFCellStyle style = workbook.createCellStyle();
			cell.setCellStyle(style);
			// 设置单元格内容为字符型
			cell.setCellValue("");
		}
	}

	/** 无样式的单元格 */
	private static void setCellValue2(XSSFRow row, int cellNum, Object dataObject) {
		XSSFCell cell = row.createCell(cellNum);
		if (dataObject != null) {
			if (dataObject instanceof Integer) {
				cell.setCellValue(Integer.parseInt(dataObject.toString()));
			} else if (dataObject instanceof Double) {
				cell.setCellValue(Double.parseDouble(dataObject.toString()));
			} else if (dataObject instanceof Long) {
				cell.setCellValue((Long) dataObject);
			} else if (dataObject instanceof Date) {
				cell.setCellValue(getCnDate((Date) dataObject));
			} else if (dataObject instanceof LocalDate) {
				cell.setCellValue(((LocalDate) dataObject).format(DateTimeFormatter.ofPattern("m/d/yy h:mm")));
			} else {
				cell.setCellValue(dataObject.toString());
			}
		} else {
			// 设置单元格内容为字符型
			cell.setCellValue("");
		}
	}

	/**
	 * 设置合并单元格并填充
	 * 
	 * @param row
	 *            行对象
	 * @param data
	 *            单元格数据
	 * @param firstRow
	 *            起始行索引
	 * @param lastRow
	 *            末尾行索引
	 * @param firstCol
	 *            起始列索引
	 * @param lastCol
	 *            末尾列索引
	 */
	public static void mergedLattice(XSSFRow row, String data, int firstRow, int lastRow, int firstCol, int lastCol) {
		Sheet sheet = row.getSheet();
		// 填充单元格
		Cell cell = row.createCell(firstCol);
		cell.setCellValue(data);
		// 设置合并单元格样式
		cell.setCellStyle(createMergedLatticeStyle(sheet.getWorkbook()));
		// 合并单元格
		CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		sheet.addMergedRegion(region);
		RegionUtil.setBorderBottom(1, region, sheet, sheet.getWorkbook()); // 下边框
		RegionUtil.setBorderLeft(1, region, sheet, sheet.getWorkbook()); // 左边框
		RegionUtil.setBorderRight(1, region, sheet, sheet.getWorkbook()); // 有边框
		RegionUtil.setBorderTop(1, region, sheet, sheet.getWorkbook()); // 上边框
	}

	public static void setTotal(XSSFRow row, List<String> datas) {
		row.setHeightInPoints(26);
		XSSFWorkbook workbook = row.getSheet().getWorkbook();
		XSSFCell cell0 = row.createCell(0);
		CellStyle cellStyle0 = workbook.createCellStyle();
		cellStyle0.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle0.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		cell0.setCellValue("合计");
		cell0.setCellStyle(cellStyle0);
		for (int i = 0; i < datas.size(); i++) {
			XSSFCell cell = row.createCell(i + 1);
			cell.setCellFormula(datas.get(i));
			cell.setCellStyle(cellStyle0);
		}
	}

	/**
	 * 向客户端返回excel文件
	 * 
	 * @param response
	 * @param wb
	 *            excel 对象
	 * @param fileName
	 *            文件名
	 * @throws IOException
	 */
	public static void sendExcelToClient(HttpServletResponse response, XSSFWorkbook wb, String fileName) throws IOException {
		// 设置文件类型 excel
		response.setContentType("application/vnd.ms-excel");
		// 设置header,否则会出现文件格式与后缀名不匹配
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf8"));
		ServletOutputStream os = response.getOutputStream();
		wb.write(os);
		os.flush();
	}

	/**
	 * 发送excel到客户端
	 * 
	 * @param response
	 * @param title
	 *            标题
	 * @param head
	 *            头部
	 * @param datas
	 *            数据
	 * @param fileName
	 *            文件名
	 * @throws IOException
	 */
	public static void sendExcel(HttpServletResponse response, String title, List<String> head, List<List<Object>> datas, String fileName) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet();
		setTitle(sheet, title, 2, head.size());
		setHead(sheet, head, 2, null);
		setDatas(sheet, datas, 3, null);
		sendExcelToClient(response, wb, fileName);
	}

	/** 列宽自适应 */
	public static void autoColumn(XSSFSheet sheet, int size) {
		// 设置为根据内容自动调整列宽
		for (int k = 0; k < size; k++) {
			assert sheet != null;
			sheet.autoSizeColumn(k);
		}
		// 处理中文不能自动调整列宽的问题
		setSizeColumn(sheet, size);
	}

	// 自适应宽度(中文支持)
	private static void setSizeColumn(XSSFSheet sheet, int size) {
		for (int columnNum = 0; columnNum < size; columnNum++) {
			int columnWidth = sheet.getColumnWidth(columnNum) / 256;
			for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
				XSSFRow currentRow;
				// 当前行未被使用过
				if (sheet.getRow(rowNum) == null) {
					currentRow = sheet.createRow(rowNum);
				} else {
					currentRow = sheet.getRow(rowNum);
				}

				if (currentRow.getCell(columnNum) != null) {
					XSSFCell currentCell = currentRow.getCell(columnNum);
					if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
						int length = currentCell.getStringCellValue().getBytes().length;
						if (columnWidth < length) {
							columnWidth = length;
						}
					}
				}
			}
			sheet.setColumnWidth(columnNum, columnWidth * 256);
		}
	}

	/**
	 * 设置列宽
	 * @param columnList 从左到右每列为几个字符的宽度
	 */
	public static void setColumnWidth(XSSFSheet sheet,int[] columnList){
		assert sheet != null;
		for (int i = 0; i < columnList.length; i++) {
			sheet.setColumnWidth(i,columnList[i]*256);
		}
	}
}
